package com.example.tool.task;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.ByteArrayOutputStream;
import org.jsoup.Jsoup;
import org.jsoup.UncheckedIOException;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author lz
 * @Date 2022/2/24 11:27
 */
public class Statistic {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    //阳泉市->地方区域->镇或街道（编号+地方区域。xls）->委会(编号+镇或者街道名.xls)
    private static   String sendHttp(String uri,String charsetName){
        try {
            URL url = new URL(uri);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setConnectTimeout(5000);
            System.out.println("连接中...");
            if(conn.getResponseCode()== 200){
                InputStream inputStream = null;
                ByteArrayOutputStream outputStream = null;
                try {
                    System.out.println("开始读取网页内容");
                    inputStream = conn.getInputStream();
                    outputStream = new ByteArrayOutputStream();
                    byte[] buffer = new byte[1024];
                    int len = 0 ;
                    while((len = inputStream.read(buffer)) != -1){
                        outputStream.write(buffer, 0, len);
                        outputStream.flush();
                    }
                    byte[] bytes = outputStream.toByteArray();
                    String charset = new String(bytes, charsetName);
                    System.out.println("读取");
                    return charset;
                } catch (IOException e) {
                    System.out.println("数据读取出错...");
                    e.printStackTrace();
                } finally{
                    outputStream.close();
                    inputStream.close();
                }
            }
        } catch (Exception e) {
            System.out.println("连接出错...");
            e.printStackTrace();
        }
        return "失败";
    }

    /**
     * 判断Excel的版本,获取Workbook
     * @return
     * @throws IOException
     */
    public static org.apache.poi.ss.usermodel.Workbook getWorkbok(File file) throws IOException{
        org.apache.poi.ss.usermodel.Workbook wb=null;
        FileInputStream in = new FileInputStream(file);
        if(file.getName().endsWith(EXCEL_XLS)){     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    public static void writeExcel(List<Map<String,String>> list,List<String> titleList){
        //开始写入excel,创建模型文件头
        String[] titleA = {"编号","名称","上级编号","等级","类别","删除标记"};
        //创建Excel文件，B库CD表文件
//        File fileA = new File("D:/Desktop/kpsw/文档/excel/"+titleList.get(0)+titleList.get(1)+".xls");
        File fileA = new File("D:/Desktop/city.xls");
        if(fileA.exists()){
            //如果文件存在
            OutputStream out = null;
            try {
                org.apache.poi.ss.usermodel.Workbook workbok = getWorkbok(fileA);
                Sheet sheetAt = workbok.getSheetAt(0);
                int row = sheetAt.getLastRowNum() ;
                for (int i = 1+row,j=1; i < list.size() + 1+row; i++) {
                    Row row1 = sheetAt.createRow(i);
                    Map<String, String> map = list.get(j - 1);
                    for (String key : map.keySet()) {
                        Cell first = row1.createCell(0);
                        first.setCellValue(key);
                        Cell second = row1.createCell(1);
                        second.setCellValue(map.get(key));
                        Cell third = row1.createCell(2);
                        third.setCellValue(titleList.get(0));
                        Cell fourth = row1.createCell(3);
                        fourth.setCellValue("4");
                        Cell fifth = row1.createCell(5);
                        fifth.setCellValue("0");
                    }
                    j++;
                }
                out =  new FileOutputStream(fileA);
                workbok.write(out);
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                try {
                    if(out != null){
                        out.flush();
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }else {
            //文件不存在就创建
            try {
                fileA.createNewFile();
                //创建工作簿
                WritableWorkbook workbookA = Workbook.createWorkbook(fileA);
                //创建sheet
                WritableSheet sheetA = workbookA.createSheet("sheet1", 0);
                Label labelA = null;
                //设置列名
                for (int i = 0; i < titleA.length; i++) {
                    labelA = new Label(i, 0, titleA[i]);
                    sheetA.addCell(labelA);
                }
                //获取数据源
                for (int i = 1; i < list.size() + 1; i++) {
                    Map<String, String> map = list.get(i - 1);
                    for (String key : map.keySet()) {
                        String value = map.get(key);
                        labelA = new Label(0, i, key);
                        sheetA.addCell(labelA);
                        labelA = new Label(1, i, value);
                        sheetA.addCell(labelA);
                        labelA = new Label(2, i, titleList.get(0));
                        sheetA.addCell(labelA);
                        labelA = new Label(3, i, "4");
                        sheetA.addCell(labelA);
                        labelA = new Label(5, i, "0");
                        sheetA.addCell(labelA);
                    }
                }
                workbookA.write();    //写入数据
                workbookA.close();  //关闭连接
                System.out.println("成功写入文件，请前往" + fileA.getAbsolutePath() + "查看文件！");
            } catch (Exception e) {
                System.out.println("文件写入失败，报异常...");
            }
        }
    }
    public  static  void getCommittee(String url, Map<String, String> higherMap){
        System.out.println("开始请求（镇或街道）url");
        String result = sendHttp(url, "UTF-8");
        List<Map<String, String>> villagetr = getLevelData(result, "villagetr");
        List<String> title =new ArrayList<>();
        for (String key:higherMap.keySet()
             ) {
        title.add(key);
        }
        //生成excel
        System.out.println("开始调用writeExcel方法");
        writeExcel(villagetr,title);
    }
    private static List<String> getDowmUrl(List<Map<String, String>> mapList, String url,int len) {
        List<String> comUrlList=new ArrayList<>();
        for (Map<String,String> map:mapList
             ) {
            String comUrl=new String();
            for (String key:map.keySet()
                 ) {
                String href = map.get(key);
                if (href.length()>0){
                    String substring = url.substring(0,url.length() - len);
                    comUrl =substring + href;
                    comUrlList.add(comUrl);
                }
                System.out.println("获取"+key+map.get(key)+"的链接中");
            }
            System.out.println("获取下级url成功");
        }
        return comUrlList;
    }
    private static List<Map<String, String>> getLevelData(String result, String elementname) throws UncheckedIOException {
        Document parse = Jsoup.parse(result);
        Elements countytable = parse.getElementsByClass(elementname);
        List<Map<String,String>> mapList=new ArrayList<>();
        for (Element element:countytable) {
            Elements elementsByAttribute = element.getElementsByTag("td");
            Map<String,String> map=new LinkedHashMap<>();
            if (elementsByAttribute.size()==2){
                for (Element elementbya:elementsByAttribute) {
                    Elements href = elementbya.getElementsByAttribute("href");
                    map.put(elementbya.text(),href.attr("href"));
                }
                mapList.add(map);
            }else {//获取最低级别数据
                map.put(elementsByAttribute.get(0).text(),elementsByAttribute.get(2).text());
                mapList.add(map);
            }
        }
        return mapList;
    }
    private static void cleanList(List<String> list){
        for (int i = 0; i <list.size() ; i++) {
            if ((i&1)==1) {//判断奇数，按位运输
                list.remove(i);
            }else {
                list.remove(i);
            }
        }
    }

    public static void main(String[] args) throws InterruptedException {
//        https://www.qcc.com/web/search?key=商丘佳健医疗器械有限
        String url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2021/43/4305.html";
        String result = sendHttp(url, "UTF-8");
        List<Map<String, String>> levelMapList = getLevelData(result, "countytr");
        List<String> townUrlList = getDowmUrl(levelMapList, url,9);
        cleanList(townUrlList);
        for (String townUrl:townUrlList) {
                Thread.sleep(1000);//第一次发送间隔一秒
            String townLevelResult = sendHttp(townUrl, "UTF-8");
            List<Map<String, String>> towntr = getLevelData(townLevelResult, "towntr");
            List<String> dowmUrlList = getDowmUrl(towntr, townUrl, 11);
            cleanList(dowmUrlList);
            for (int i = 0; i <towntr.size() ; i++) {
                    Thread.sleep(5000);//再一次查询下级先暂停五秒
                getCommittee(dowmUrlList.get(i), towntr.get(i));
            }
        }
    }


}
